/*
 * Copyright (C) 2013 Michael
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 */
package com.jm.jxl.xporta;

import com.jm.commons.fio.FileTypeFilter;
import com.jm.commons.ui.UIBounds;
import com.jm.xporta.XPortException;
import com.jm.xporta.XPorta;
import com.jm.xporta.plugin.XPort;
import com.jm.xporta.utils.TypeHandler;
import java.io.File;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Date;
import java.util.Locale;
import javax.imageio.ImageIO;
import javax.swing.filechooser.FileFilter;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

/**
 *
 * @created Feb 12, 2013
 * @author Michael L.R. Marques
 */
public class JxlXPorta extends XPorta implements XPort {

    /**
     *
     * @param results
     * @throws com.jm.xporta.XPortException
     */
    @Override
    public void initialize(ResultSet results) throws XPortException {
        super.initialize(results);
        UIBounds.setBounds(this);
        // Set the icon image
        try {
            setIconImage(ImageIO.read(getClass().getClassLoader().getResource("com/jm/jmsql/icons/xport.png")));
        } catch (IOException ioe) {
        }
        setVisible(true);
    }

    /**
     *
     * @return
     */
    @Override
    public String getName() {
        return "Xls XPorta";
    }

    /**
     *
     * @return
     */
    @Override
    public String getDescription() {
        return "Utilizes the JExcelAPI and exports database data to a *.xls file.";
    }

    /**
     *
     * @return
     */
    @Override
    public FileFilter[] getFileFilters() {
        return new FileFilter[]{new FileTypeFilter("xls", "Excel 97-2003 Workbook")};
    }

    /**
     *
     * @throws com.jm.xporta.XPortException
     */
    @Override
    public void export() throws XPortException {
        // If data is null throw an exception
        if (getData() == null) {
            throw new XPortException("XPort data was not initialized");
        }
        // If the output file does not exist, throw an exception
        if (getOutput() == null) {
            throw new XPortException("Output file was not initialized");
        }
        // Initialize needed variables
        ResultSet results = getData();
        int totalRecords = 0;
        int totalColumns = 0;
        String[] columnNames;
        int[] columnTypes;
        setProgressMinimum(0);
        setProgressValue(0);

        // Create the workbook and initializes it's settings
        WorkbookSettings wbSettings = new WorkbookSettings();
        wbSettings.setLocale(new Locale("en", "EN"));
        WritableWorkbook workbook = null;
        try {
            workbook = Workbook.createWorkbook((File) getOutput(), wbSettings);
        } catch (IOException ioe) {
            throw new XPortException(ioe.getMessage(), ioe.getCause());
        }
        try {
            if (workbook != null) {
                // Collect the result sets infromation
                try {
                    results.last();
                    totalRecords = results.getRow();
                    ResultSetMetaData meta = results.getMetaData();
                    totalColumns = meta.getColumnCount();
                    columnNames = new String[totalColumns];
                    columnTypes = new int[totalColumns];
                    for (int i = 0; i < totalColumns; i++) {
                        columnNames[i] = meta.getColumnName(i + 1);
                        columnTypes[i] = meta.getColumnType(i + 1);
                    }
                    results.beforeFirst();
                    setProgressMaximum(totalRecords);
                } catch (SQLException sqle) {
                    throw new XPortException(sqle.getMessage(), sqle.getCause());
                }
                // Calculate the number of sheets that we are going to need
                int sheets = (int) Math.round(totalRecords / Math.pow(2, 16)) + 1;
                for (int i = 0; i < sheets; i++) {
                    // Create the sheet
                    WritableSheet sheet = workbook.createSheet("Exported Sheet " + (i + 1), i);
                    // Create the sheet header
                    for (int column = 0; column < totalColumns; column++) {
                        try {
                            sheet.addCell(new Label(column, 0, columnNames[column]));
                        } catch (Exception e) {
                            throw new XPortException(e.getMessage(), e.getCause());
                        }
                    }
                    // Start writting the data to the sheet
                    try {
                        for (int row = 1; row < Math.pow(2, 16) && results.next(); row++) {
                            for (int column = 0; column < totalColumns; column++) {
                                try {
                                    sheet.addCell(createCell(column, row, columnTypes[column], results));
                                } catch (SQLException | WriteException e) {
                                    throw new XPortException(e.getMessage(), e.getCause());
                                }
                            }
                            setProgressValue(getProgressValue() + 1);
                        }
                    } catch (SQLException sqle) {
                        throw new XPortException(sqle.getMessage(), sqle.getCause());
                    }
                }
                try {
                    workbook.write();
                } catch (IOException ioe) {
                    throw new XPortException(ioe.getMessage(), ioe.getCause());
                }
            }
        } finally {
            try {
                workbook.close();
            } catch (IOException | WriteException e) {
                throw new XPortException(e.getMessage(), e.getCause());
            }
            workbook = null;
        }
    }

    /**
     *
     * @param x
     * @param y
     * @param value
     * @return
     */
    private static WritableCell createCell(int column, int row, int type, ResultSet results) throws SQLException {
        Class javaType = TypeHandler.jdbcToJavaTypes(type);
        if (javaType.equals(Double.class)) {
            return new jxl.write.Number(column, row, results.getDouble(column + 1));
        } else if (javaType.equals(Float.class)) {
            return new jxl.write.Number(column, row, results.getFloat(column + 1));
        } else if (javaType.equals(Long.class)) {
            return new jxl.write.Number(column, row, results.getLong(column + 1));
        } else if (javaType.equals(Integer.class)) {
            return new jxl.write.Number(column, row, results.getInt(column + 1));
        } else if (javaType.equals(Date.class)) {
            return new jxl.write.DateTime(column, row, results.getDate(column + 1));
        } else if (javaType.equals(Boolean.class)) {
            return new jxl.write.Boolean(column, row, results.getBoolean(column + 1));
        } else if (javaType.equals(String.class)) {
            return new Label(column, row, results.getString(column + 1));
        } else {
            Object object = results.getObject(column + 1);
            return new Label(column, row, object != null ? object.toString() : "");
        }
    }
}
